﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.SqlClient;
using System.Text;
using IDAL;
using Models;

namespace DAL
{
    public class WorksPicInfoDAL:IWorksPicInfo
    {
        public const string SQL_WORKS_PIC = @"select PicID,WorksID,PicName,PicURL,PicLevel,TopFlag 
                                              from WorksPicInfo where WorksID = ";
        public const string SQL_MOVE_PIC = @" and PicID not in (select top 1 PicID from WorksPicInfo where WorksID =";

        public const string SQL_ORDER_BY_DESC = @" order by PicLevel desc)";

        public DataBase DB = new DataBase(); 


        public List<WorksPicInfo> GetWorkAllPicInfo(int WorksID)
        {
            List<WorksPicInfo> list = new List<WorksPicInfo>();
            DB.DatabaseConnection();
            SqlDataReader reader = DB.GetdataRecords(SQL_WORKS_PIC + WorksID + SQL_MOVE_PIC + WorksID + SQL_ORDER_BY_DESC + " ORDER BY PicLevel DESC ");
            while (reader.Read())
            {
                WorksPicInfo workspicinfo = new WorksPicInfo();
                ReadtoWorksPicInfo(reader, workspicinfo);
                list.Add(workspicinfo);
            }
            DB.CloseDB();
            return list;
        }

        public void ReadtoWorksPicInfo(SqlDataReader reader, WorksPicInfo workspicinfo)
        {
            try
            {
                workspicinfo.PicID = Int32.Parse(reader["PicID"].ToString());
            }
            catch
            { }
            try
            {
                workspicinfo.WorksID = Int32.Parse(reader["WorksID"].ToString());
            }
            catch
            { }
            try
            {
                workspicinfo.PicLevel = Int32.Parse(reader["PicLevel"].ToString());
            }
            catch { }
            workspicinfo.PicName =reader["PicName"] is DBNull?"":reader["PicName"].ToString();
            workspicinfo.PicURL =reader["PicURL"] is DBNull?"":reader["PicURL"].ToString();
            
        }

        public int AddWorksPicInfo(WorksPicInfo pic)
        {
            int isSuccess = -1;
            string sql = @"INSERT INTO WorksPicInfo ([WorksID],[PicName],[PicURL],[TopFlag],[PicLevel]) 
                        VALUES(" + pic.WorksID + ",'" + pic.PicName + "','" + pic.PicURL + "'," + pic.TopFlag + "," + pic.PicLevel + ");";
            isSuccess = DB.UpdateRecords(sql);
            return isSuccess;        
        }
        public WorksPicInfo GetTopOneWorksPics(int wid)
        {
            WorksPicInfo pic = new WorksPicInfo();
            string sql = @"SELECT top 1 * FROM WorksPicInfo WHERE WorksID = " + wid + " ORDER BY PicLevel DESC ";
            DB.DatabaseConnection();
            SqlDataReader reader = DB.GetdataRecords(sql);
            while (reader.Read())
            {
                ReadtoWorksPicInfo(reader, pic);
            }
            DB.CloseDB();
            return pic;
        }

        public WorksPicInfo GetOneWorksPicInfo(int pid)
        {
            WorksPicInfo pic = new WorksPicInfo();
            string sql = "SELECT * FROM WorksPicInfo WHERE PicID = " + pid;
            DB.DatabaseConnection();
            SqlDataReader reader = DB.GetdataRecords(sql);
            while (reader.Read())
            {
                ReadtoWorksPicInfo(reader,pic);
            }
            DB.CloseDB();
            return pic;
        }
        public int DeleteWorksPicInfo(int PicID)
        {
            int isSuccess = -1;
            string sql = "DELETE WorksPicInfo WHERE PicID=" + PicID;
            isSuccess = DB.UpdateRecords(sql);
            return isSuccess;
        }
        public WorksPicInfo GetOneWorksPicInfoFromURL(string url)
        {
            WorksPicInfo pic = new WorksPicInfo();
            string sql = "SELECT * FROM WorksPicInfo WHERE PicURL = '" + url + "'";
            DB.DatabaseConnection();
            SqlDataReader reader = DB.GetdataRecords(sql);
            while (reader.Read())
            {
                ReadtoWorksPicInfo(reader, pic);
            }
            DB.CloseDB();
            return pic;
        }

        public int UpdateWorksPicInfo(WorksPicInfo pic)
        {
            int isSuccess = -1;
            string sql = @"UPDATE WorksPicInfo SET 
                        [WorksID] = " + pic.WorksID + @",
                        [PicName] = '" + pic.PicName + @"',
                        [PicURL] = '" + pic.PicURL + @"',
                        [PicLevel] = " + pic.PicLevel + @"
                        WHERE PicID =" + pic.PicID;
            isSuccess = DB.UpdateRecords(sql);
            return isSuccess;
        }
    }
}
